﻿using DB;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace TrainTicketBookingSystem
{
    internal class TrainSequenceService
    {
        private StationService stationService = new StationService();
        public List<TrainSequence> FindTrainSqeuenceByTimeAndStation(DateTime launchTime,string startStation,string endStation)
        {
            List<TrainSequence> trainSequencesList = new List<TrainSequence>();

            int StartStationId = stationService.FindStationIdByName(startStation);
            int EndStationId = stationService.FindStationIdByName(endStation);

            string sql = " select * from trainSequence where DATE(launchTime) = DATE(@launchTime) " +
                " and startStation = @StartStationId " +
                " and endStation = @EndStationId ";

            MySqlDataReader reader = MySqlUtil.ExecutQuery(sql,
                new MySqlParameter("@launchTime", launchTime),
                new MySqlParameter("@StartStationId", StartStationId),
                new MySqlParameter("@EndStationId", EndStationId));

            while (reader.Read())
            {
                int trainSequenceId = reader.GetInt32("trainSequenceId");
                string trainNum = reader.GetString("trainNum");
                int trainId = reader.GetInt32("trainId");
                int StartStation = reader.GetInt32("startStation");
                int EndStation = reader.GetInt32("endStation");
                DateTime LaunchTime = reader.GetDateTime("launchTime");
                DateTime ArrivalTime = reader.GetDateTime("arrivalTime");
                TimeSpan travelTime = reader.GetTimeSpan("travelTime");

                trainSequencesList.Add(new TrainSequence(
                    trainSequenceId,trainNum,trainId,StartStation,EndStation,LaunchTime,ArrivalTime,travelTime));
            }

            return trainSequencesList;
        }

        public void UpdateByTimeAndStation(DateTime launchTime, string startStation, string endStation, List<TrainSequence> trainSequencesList)
        {
            DeleteByTimeAndStation(launchTime, startStation, endStation);
            CreateData(trainSequencesList);
        }

        public void DeleteByTimeAndStation(DateTime launchTime, string startStation, string endStation)
        {
            int StartStationId = stationService.FindStationIdByName(startStation);
            int EndStationId = stationService.FindStationIdByName(endStation);

            string sql = " delete from trainSequence where DATE(launchTime) = DATE(@launchTime) " +
                        " and startStation = @StartStationId " +
                        " and endStation = @EndStationId ";

            MySqlUtil.ExecutQuery(sql, new MySqlParameter("@launchTime", launchTime),
                new MySqlParameter("@StartStationId", StartStationId),
                new MySqlParameter("@EndStationId", EndStationId));
        }

        public void CreateData(List<TrainSequence> trainSequencesList)
        {
            string sql = " insert trainSequence(trainNum,trainId,startStation,endStation,launchTime,arrivalTime) " +
                " VALUES(@trainNum,@trainId,@startStation,@endStation,@launchTime,@arrivalTime) ";

            foreach (TrainSequence ts in trainSequencesList)
            {
                MySqlUtil.ExecutQuery(sql, new MySqlParameter("@trainNum", ts.TrainNum),
                    new MySqlParameter("@trainId", ts.TrainId),
                    new MySqlParameter("@startStation", ts.StartStation),
                    new MySqlParameter("@endStation", ts.EndStation),
                    new MySqlParameter("@launchTime", ts.LaunchTime),
                    new MySqlParameter("@arrivalTime",ts.ArriveTime));
            }
        }

        public TrainSequence FindTrainSequenceByTrainNum(string TrainNum)
        {
            string sql = " select * from trainSequence where trainNum = @trainNum ";

            MySqlDataReader reader = MySqlUtil.ExecutQuery(sql, new MySqlParameter("@trainNum", TrainNum));

            while (reader.Read())
            {
                int trainSequenceId = reader.GetInt32("trainSequenceId");
                string trainNum = reader.GetString("trainNum");
                int trainId = reader.GetInt32("trainId");
                int StartStation = reader.GetInt32("startStation");
                int EndStation = reader.GetInt32("endStation");
                DateTime LaunchTime = reader.GetDateTime("launchTime");
                DateTime ArrivalTime = reader.GetDateTime("arrivalTime");
                TimeSpan travelTime = reader.GetTimeSpan("travelTime");

                return new TrainSequence(trainSequenceId, trainNum, trainId, StartStation, EndStation, LaunchTime, ArrivalTime, travelTime);
            }

            return null;
        }

        public TrainSequence FindTrainSequenceById(int id)
        {
            string sql = " select * from trainSequence where trainSequenceId = @id ";

            MySqlDataReader reader = MySqlUtil.ExecutQuery(sql, new MySqlParameter("@id", id));

            while (reader.Read())
            {
                int trainSequenceId = reader.GetInt32("trainSequenceId");
                string trainNum = reader.GetString("trainNum");
                int trainId = reader.GetInt32("trainId");
                int StartStation = reader.GetInt32("startStation");
                int EndStation = reader.GetInt32("endStation");
                DateTime LaunchTime = reader.GetDateTime("launchTime");
                DateTime ArrivalTime = reader.GetDateTime("arrivalTime");
                TimeSpan travelTime = reader.GetTimeSpan("travelTime");

                return new TrainSequence(trainSequenceId, trainNum, trainId, StartStation, EndStation, LaunchTime, ArrivalTime, travelTime);
            }

            return null;
        }
    }
}
